
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_DimDeliveryType_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_DimDeliveryType_Insert]
GO


create procedure dbo.p_DimDeliveryType_Insert (
	@DeliveryTypeIdentifier varchar (30),
	@DeliveryTypeFullName nvarchar (150),
	@DistributionCompany nvarchar (100),
	@IsInternational varchar (3),
	@DeliveryPeriod nvarchar (50),
	@DeliveryTypeKey int output)
as
begin

	-- Disable row counting
	set nocount on

	declare @Error int,
	        @inTranCount int,
	        @Rows int

	select @Error = 0,
	       @inTranCount = @@trancount

	-- Only start our own transaction if caller has not supplied one
	if (@inTranCount = 0) begin transaction txnDimDeliveryTypeInsert
        else
	    save transaction txnDimDeliveryTypeInsert

	set @Error = @@error
	if (@Error <> 0) goto cleanup

	insert into dbo.DimDeliveryType (DeliveryTypeIdentifier,
					 DeliveryTypeFullName,
					 DistributionCompany,
					 IsInternational,
					 DeliveryPeriod)
	  values (@DeliveryTypeIdentifier,
		  @DeliveryTypeFullName,
		  @DistributionCompany,
		  @IsInternational,
		  @DeliveryPeriod)

	select @Error = @@error,
	       @Rows = @@rowcount
	if ((@Error = 0) and
	    (@Rows = 0)) set @Error = -1
	if (@Error <> 0) goto cleanup

	set @DeliveryTypeKey = scope_identity()

cleanup:

	-- End transaction
	if (@@trancount > 0)
	begin

		-- On error roll back to save point or local transaction else commit
		if (@Error <> 0) rollback transaction txnDimDeliveryTypeInsert
		else
		if (@inTranCount = 0) commit transaction txnDimDeliveryTypeInsert

	end

	return @Error

end
GO

grant execute on dbo.p_DimDeliveryType_Insert  to [db_datawriter]
go
